﻿

-- 计算人员工时
create procedure [dbo].[ComputeAllRyGs]
@ReCompute bit -- 0:不 1:重算
as
set nocount on
if(@ReCompute=1)
begin
delete from Compute_Result
delete from Compute_Rygs
end
if not exists(select 1 from Compute_Result)
begin
	insert into Compute_Result values(0,1)
end
declare 
@sfzh varchar(18),
@maxlsh bigint
select @maxlsh=max(kqlsh) from kqjl_info where kqsj<=(select max(kqsj) from vwRyFilterKqjl)
DECLARE Ry_Cursor CURSOR FOR
SELECT distinct sfzh FROM vwRyFilterKqjl;
OPEN Ry_Cursor;
FETCH NEXT FROM Ry_Cursor into @sfzh;
WHILE @@FETCH_STATUS = 0
   BEGIN
/**************************************************************************************************************************
根据实际进出算工时
**************************************************************************************************************************/
	declare @maxkqlsh bigint
	select sfzh,xm,qybmm,sjdbegin,sjdend,jczt,kqsj into #tbs from vwRyFilterKqjl where sfdj=0 and sfzh=@sfzh
	declare @sjdbegin datetime,
			@sjdend datetime
	declare	@kqjl table(sfzh varchar(18),xm varchar(10),qybmm varchar(100),jkqsj datetime,ckqsj datetime)
	DECLARE Kqjl_Cursor CURSOR FOR
	select distinct sjdbegin,sjdend from #tbs
	open Kqjl_Cursor;
	FETCH NEXT FROM Kqjl_Cursor into @sjdbegin,@sjdend
	WHILE @@FETCH_STATUS = 0
	BEGIN
	select identity(int,1,1) id,sfzh,xm,qybmm,sjdbegin,sjdend,jczt,kqsj into #tb from #tbs 
	where sjdbegin=@sjdbegin and sjdend=@sjdend order by kqsj
	-- 删除第一条不是进 drop table #tb
	delete from #tb where id=(select id from (select top 1 id,jczt from #tb order by id) aa where jczt<>1)
	-- 删除最后一条不是出
	delete from #tb where id=(select id from (select top 1 id,jczt from #tb order by id desc) aa where jczt<>0)
	select identity(int,1,1) id,sfzh,xm,qybmm,sjdbegin,sjdend,jczt,kqsj into #temp from #tb order by kqsj
	drop table #tb
	delete from #temp where id in(
	select bb.id from #temp aa left join #temp bb on(aa.id=bb.id+1) where aa.jczt ^ bb.jczt=0 and bb.jczt=1
	union 
	select aa.id from #temp aa left join #temp bb on(aa.id=bb.id+1)  where aa.jczt ^ bb.jczt=0 and aa.jczt=0
	)
	select identity(int,1,1) id,sfzh,xm,qybmm,kqsj into #aa from #temp where jczt=1
	select identity(int,1,1) id,sfzh,xm,qybmm,kqsj into #bb from #temp where jczt=0
	drop table #temp
	select #aa.id,#aa.sfzh,#aa.xm,#aa.qybmm,#aa.kqsj jkqsj,#bb.kqsj ckqsj into #cc from #aa
	left join #bb on(#aa.id=#bb.id) where #bb.id is not null
	drop table #aa
	drop table #bb
	-- 累计
	insert into @kqjl
	select sfzh,xm,qybmm,jkqsj,ckqsj from #cc
	drop table #cc
	FETCH NEXT FROM Kqjl_Cursor into @sjdbegin,@sjdend;
	END;
	CLOSE Kqjl_Cursor;
	DEALLOCATE Kqjl_Cursor;
	drop table #tbs
	-- 生成考勤工时记录
	insert into Compute_Rygs(sfzh,xm,qybmm,ljgs,kqsj,jslx)
	select sfzh,xm,qybmm,sum(datediff(ss,jkqsj,ckqsj)) zgs,
	convert(varchar(10),jkqsj,120) kqsj,0
	from @kqjl
	group by sfzh,xm,qybmm,convert(varchar(10),jkqsj,120)
	delete from @kqjl

/**************************************************************************************************************************
根据记录奇偶来算（必须为单门单向且配对)
**************************************************************************************************************************/

 FETCH NEXT FROM Ry_Cursor into @sfzh;
   END;
CLOSE Ry_Cursor;
DEALLOCATE Ry_Cursor;
update Compute_Result set yjskqjllsh=@maxlsh,jszt=1
set nocount off